OraFAQ Forum: SQL & PL/SQL » Row Counter in Select statement

Home » SQL & PL/SQL » SQL & PL/SQL » Row Counter in Select statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row Counter in Select statement [message #9275]

Thu, 30 October 2003 06:25

Rob
Messages: 70
Registered: January 2000

Member

Dear board,

Is there a way to include a cumulative counter in a select statement? So that the resultset contains a column starting with 1, 2, 3...n. Counting the rows as the select executes? (I can't use a cursor or stored proc for office political reasons, stupid I know!)

Many, many thanks,
Rob

Report message to a moderator

Re: Row Counter in Select statement [message #9278 is a reply to message #9275]

Thu, 30 October 2003 07:38

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Have you tried the ROWNUM pseudocolumn?

SQL> SELECT ROWNUM
  2  ,      e.*
  3  FROM   emp  e
  4  /
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 12/17/1980        800                    20
         2       7499 ALLEN      SALESMAN        7698 2/20/1981        1600        300         30
         3       7521 WARD       SALESMAN        7698 2/22/1981        1250        500         30
         4       7566 JONES      MANAGER         7839 4/2/1981         2975                    20
         5       7654 MARTIN     SALESMAN        7698 9/28/1981        1250       1400         30
         6       7698 BLAKE      MANAGER         7839 5/1/1981         2850                    30
         7       7782 CLARK      MANAGER         7839 6/9/1981         2450                    10
         8       7788 SCOTT      ANALYST         7566 12/9/1982        3000                    20
         9       7839 KING       PRESIDENT            11/17/1981       5000                    10
        10       7844 TURNER     SALESMAN        7698 9/8/1981         1500          0         30
        11       7876 ADAMS      CLERK           7788 1/12/1983        1100                    20
        12       7900 JAMES      CLERK           7698 12/3/1981         950                    30
        13       7902 FORD       ANALYST         7566 12/3/1981        3000                    20
        14       7934 MILLER     CLERK           7782 1/23/1982        1300                    10
  
14 rows selected.
  
SQL>

For more on what ROWNUM is (and what it is not), see this "Ask Tom" discussion.

HTH,

Art.

Report message to a moderator

Re: Row Counter in Select statement [message #9279 is a reply to message #9278]

Thu, 30 October 2003 08:34

Rob
Messages: 70
Registered: January 2000

Member

Thanks Art,

Further to that, is there anyway to re-set the rownum?

Say for example that the EMPNO column contains two consecutive rows of 7499, could the row counter be '1' for the first instance, then '2' for the second. Some sort of (1 of 2) counter?

Thanks again,
Rob

Report message to a moderator

Re: Row Counter in Select statement [message #9280 is a reply to message #9279]

Thu, 30 October 2003 08:58

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Rob,

Try the ROW_NUMBER analytic function:

SQL> SELECT   e.empno
  2  ,        e.ename
  3  ,        e.job
  4  ,        e.mgr
  5  ,        e.hiredate
  6  ,        e.sal
  7  ,        e.comm
  8  ,        e.deptno
  9  ,        ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY hiredate) rn
 10  FROM     emp  e
 11  ORDER BY e.deptno
 12  ,        e.hiredate
 13  /
  
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 6/9/1981         2450                    10          1
      7839 KING       PRESIDENT            11/17/1981       5000                    10          2
      7934 MILLER     CLERK           7782 1/23/1982        1300                    10          3
      7369 SMITH      CLERK           7902 12/17/1980        800                    20          1
      7566 JONES      MANAGER         7839 4/2/1981         2975                    20          2
      7902 FORD       ANALYST         7566 12/3/1981        3000                    20          3
      7788 SCOTT      ANALYST         7566 12/9/1982        3000                    20          4
      7876 ADAMS      CLERK           7788 1/12/1983        1100                    20          5
      7499 ALLEN      SALESMAN        7698 2/20/1981        1600        300         30          1
      7521 WARD       SALESMAN        7698 2/22/1981        1250        500         30          2
      7698 BLAKE      MANAGER         7839 5/1/1981         2850                    30          3
      7844 TURNER     SALESMAN        7698 9/8/1981         1500          0         30          4
      7654 MARTIN     SALESMAN        7698 9/28/1981        1250       1400         30          5
      7900 JAMES      CLERK           7698 12/3/1981         950                    30          6
  
14 rows selected.
  
SQL>

HTH,

A.

Report message to a moderator

Re: Row Counter in Select statement (THANKS ART!) [message #9281 is a reply to message #9280]

Thu, 30 October 2003 09:06

Rob
Messages: 70
Registered: January 2000

Member

Thanks Art, that's so helpful, exactly what I was after.

Peace be with you.

Report message to a moderator

Previous Topic:	A sql question
Next Topic:	Test tool advice

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 08:10:59 CDT 2024